package com.ivo.service.pdm;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Service;

import com.ivo.model.UserRole;
import com.ivo.service.BaseService;

@Service
public class UserRoleService extends BaseService<UserRole>
{

	public List<UserRole> getUserRoles(String roleId)
	{
		String hql = "from UserRole a where a.role.roleId = ? and a.category.cateId is null and a.isValid = 1";
		List list = hibernateCaller.get( hql, roleId );
		return list;
	}

	public List<UserRole> getUserRoles(String roleId, Long cateId)
	{
		String hql = "from UserRole a where a.role.roleId = ? and a.category.cateId = ? and a.isValid = 1";
		List list = hibernateCaller.get( hql, roleId, cateId );
		return list;
	}

	public List<UserRole> getUserRolesByCateRoleId(Long cateId, String roleId)
	{
		StringBuffer sbSql = new StringBuffer();

		sbSql.append( " select * " );
		sbSql.append( " from PDM_R_USER_ROLE " );
		sbSql.append( " where CATE_FK in ( " );
		sbSql.append( " select CATE_FK " );
		sbSql.append( " from PDM_R_CATE_PATH " );
		sbSql.append( " where CATE_FK <> " );
		sbSql.append( cateId );
		sbSql.append( " and ( LV01 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV02 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV03 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV04 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV05 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV06 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV07 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV08 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV09 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV10 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV11 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV12 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV13 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV14 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV15 = " );
		sbSql.append( cateId );
		sbSql.append( " ) UNION ALL " );
		sbSql.append( " select ISNULL(LV05 , '0') AS CATE_FK " );
		sbSql.append( " from PDM_R_CATE_PATH " );
		sbSql.append( " where CATE_FK = " );
		sbSql.append( cateId );
		sbSql.append( " ) and ROLE_FK = '" );
		sbSql.append( roleId );
		sbSql.append( "' and IS_VALID = 1 " );

		List list = hibernateCaller.sqlQuery( sbSql.toString(), UserRole.class );
		return list;
	}

	public List<UserRole> getUserRolesById(Long cateId, String roleId)
	{
		StringBuffer sbSql = new StringBuffer();

		sbSql.append( " select UR.* " );
		sbSql.append( " from PDM_D_AUTH_ROLE as AR inner join PDM_R_USER_ROLE as UR " );
		sbSql.append( " on AR.CATE_FK IN ( " );
		sbSql.append( " select CATE_FK " );
		sbSql.append( " from PDM_R_CATE_PATH " );
		sbSql.append( " where CATE_FK <> " );
		sbSql.append( cateId );
		sbSql.append( " and ( LV01 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV02 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV03 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV04 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV05 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV06 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV07 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV08 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV09 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV10 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV11 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV12 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV13 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV14 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV15 = " );
		sbSql.append( cateId );
		sbSql.append( " ) UNION ALL " );
		sbSql.append( " select ISNULL(LV05 , '0') AS CATE_FK " );
		sbSql.append( " from PDM_R_CATE_PATH " );
		sbSql.append( " where CATE_FK = " );
		sbSql.append( cateId );
		sbSql.append( " ) and AR.ROLE_FK = '" );
		sbSql.append( roleId );
		sbSql.append( "' and AR.IS_VALID = 1 and UR.IS_VALID = 1 and AR.CATE_FK = UR.CATE_FK and AR.ROLE_FK = UR.ROLE_FK " );

		List list = hibernateCaller.sqlQuery( sbSql.toString(), UserRole.class );
		return list;
	}

	public boolean bAdministrator(String userId)
	{
		StringBuffer sbSQL = new StringBuffer();
		boolean bAuth = false;

		sbSQL.append( " SELECT COUNT(*) AS NUM FROM ID_R_USER_ROLE WHERE USER_FK = '" );
		sbSQL.append( userId );
		sbSQL.append( "' AND IS_VALID = 1 AND ROLE_FK = 'R00001' " );
		List list = hibernateCaller.sqlGetSingleResult( sbSQL.toString(), "NUM" );

		String result = (String) list.get( 0 );
		bAuth = ( Integer.parseInt( result ) > 0 );

		return bAuth;
	}

	public boolean bAuthDccDoc(String userId)
	{
		StringBuffer sbSQL = new StringBuffer();
		boolean bAuth = false;

		sbSQL.append( " SELECT COUNT(*) AS NUM FROM ID_R_USER_ROLE WHERE USER_FK = '" );
		sbSQL.append( userId );
		sbSQL.append( "' AND IS_VALID = 1 AND DCC_READ = 1 " );
		List list = hibernateCaller.sqlGetSingleResult( sbSQL.toString(), "NUM" );

		String result = (String) list.get( 0 );
		bAuth = ( Integer.parseInt( result ) > 0 );

		return bAuth;
	}

	public boolean bProjectRole(String userId, String type)
	{
		StringBuffer sbSQL = new StringBuffer();
		boolean bAuth = false;

		sbSQL.append( " select count(*) as NUM " );
		sbSQL.append( " from ( " );
		sbSQL.append( " select CATE_FK,EMPLOYEE_FK " );
		sbSQL.append( " from PDM_D_AUTH_EMP as AE inner join PDM_O_CATE as C1 " );
		sbSQL.append( " on AE.CATE_FK = C1.CATE_ID " );
		sbSQL.append( " and C1.CATE_TYPE_FK = '" );
		sbSQL.append( type );
		sbSQL.append( "' " );
		sbSQL.append( " and AE.IS_VALID = 1 and C1.IS_VALID = 1	and EMPLOYEE_FK = '" );
		sbSQL.append( userId );
		sbSQL.append( "' union all " );
		sbSQL.append( " select AR.CATE_FK,UR.USER_FK AS EMPLOYEE_FK " );
		sbSQL.append( " from PDM_R_USER_ROLE as UR inner join PDM_D_AUTH_ROLE as AR " );
		sbSQL.append( " on AR.ROLE_FK = UR.ROLE_FK and AR.IS_VALID = 1 " );
		sbSQL.append( " and UR.USER_FK = '" );
		sbSQL.append( userId );
		sbSQL.append( "' inner join PDM_O_CATE as C on AR.CATE_FK = C.CATE_ID and C.CATE_TYPE_FK = '" );
		sbSQL.append( type );
		sbSQL.append( "' ) as tbl " );

		List list = hibernateCaller.sqlGetSingleResult( sbSQL.toString(), "NUM" );

		String result = (String) list.get( 0 );
		bAuth = ( Integer.parseInt( result ) > 0 );

		return bAuth;
	}

	public ArrayList<String> getUserListByCateRoleId(Long cateId, String roleId)
	{
		StringBuffer sbSql = new StringBuffer();
		ArrayList<String> returnList = new ArrayList<String>();

		sbSql.append( " select distinct USER_FK " );
		sbSql.append( " from PDM_R_USER_ROLE " );
		sbSql.append( " where CATE_FK in ( " );
		sbSql.append( " select CATE_FK " );
		sbSql.append( " from PDM_R_CATE_PATH " );
		sbSql.append( " where ( LV01 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV02 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV03 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV04 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV05 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV06 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV07 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV08 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV09 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV10 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV11 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV12 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV13 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV14 = " );
		sbSql.append( cateId );
		sbSql.append( " or LV15 = " );
		sbSql.append( cateId );
		sbSql.append( " ) ) and ROLE_FK = '" );
		sbSql.append( roleId );
		sbSql.append( "' and IS_VALID = 1 " );

		List list = hibernateCaller.sqlGetSingleResult( sbSql.toString(), "USER_FK" );

		if( list.size() > 0 )
		{
			for( int i = 0; i < list.size(); i++ )
			{
				String id = (String) list.get( i );
				returnList.add( id );
			}
		}

		return returnList;
	}

	public boolean bPJMMember(String userId)
	{
		StringBuffer sbSQL = new StringBuffer();
		boolean bAuth = false;

		sbSQL.append( " SELECT COUNT(*) AS NUM FROM ID_R_USER_ROLE WHERE USER_FK = '" );
		sbSQL.append( userId );
		sbSQL.append( "' AND IS_VALID = 1 AND ROLE_FK = 'R00003' " );
		List list = hibernateCaller.sqlGetSingleResult( sbSQL.toString(), "NUM" );

		String result = (String) list.get( 0 );
		bAuth = ( Integer.parseInt( result ) > 0 );

		return bAuth;
	}

}
